package com.my.zl.common.utils;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;

/**
 * 高级查询处理,如果不用表名的话，后面再整理
 * @author zhouliang
 *
 */
public class QueryFilterUtils {

    // 内部运算符关系
    private static Map<String, String> FILTER_MAP = new HashMap<>();

    // 运算符格式，左右至少一个空格
    private static final Pattern FILTER_RULE = Pattern.compile(" {1,}(lk|nk|eq|nq|lt|le|gt|ge|cn) {1,}");
   
    // 特殊字符 和空格拦截(放开了空格则需要严格校验吧)
//    private static final Pattern SPECIAL_FILTER_RULE = Pattern.compile("[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？\\s]");
    private static final Pattern SPECIAL_FILTER_RULE = Pattern.compile("\\b(and|exec|insert|select|drop|grant|alter|delete|update|count|chr|mid|master|truncate|char|declare|or)\\b"
            + "|(\\*|;|\\+|'|%)",Pattern.CASE_INSENSITIVE);
    
    // 字段名过滤,只有字母+数字才正确
    private static final Pattern FIELD_FILTER_RULE = Pattern.compile("[^a-z0-9A-Z]+");

    // 时间格式校验
    private static final Pattern TIME_CHECK_RULE = Pattern.compile("^TIME\\(([0-9]{4}|[0-9]{6}|[0-9]{8}|[0-9]{14})\\)$",Pattern.CASE_INSENSITIVE);

    // 时间格式
    private static Map<String, String> TIME_FORMAT_MAP = new HashMap<>();

    static {
        // 暂时就这样
        FILTER_MAP.put("lk", "like");
        FILTER_MAP.put("nk", "not like");
        FILTER_MAP.put("eq", "=");
        FILTER_MAP.put("nq", "!=");
        FILTER_MAP.put("lt", "<");
        FILTER_MAP.put("le", "<=");
        FILTER_MAP.put("gt", ">");
        FILTER_MAP.put("ge", ">=");
        FILTER_MAP.put("cn", "in");
        
        TIME_FORMAT_MAP.put("4", "%Y");// 年
        TIME_FORMAT_MAP.put("6", "%Y%m");// 年月
        TIME_FORMAT_MAP.put("8", "%Y%m%d");// 年月日
        TIME_FORMAT_MAP.put("14", "%Y%m%d%H%i%s");// 年月日时分秒
    }

    /**
     * 查询条件永远不会有时间查询的无需传入时区
     * @param filter
     * @param timeZone
     * @return
     */
    public static String filterConvert(String filter) {
        return filterConvertWithTimeZone(filter,null);
    }
    
    /**
     * 查询条件可能带时间查询的需要传入时区(推荐都用这个)
     * @param filter
     * @param timeZone
     * @return
     */
    public static String filterConvertWithTimeZone(String filter,String timeZone) {
        if (StrUtil.isBlank(filter)) {
            return null;
        }
        
        String[] splitStr = {"and","or"};
        Pattern splitRule1 = Pattern.compile(" {1,}(%"+splitStr[0]+"){1,} {1,}",Pattern.CASE_INSENSITIVE);
        Pattern splitRule2 = Pattern.compile(" {1,}(%"+splitStr[1]+"){1,} {1,}",Pattern.CASE_INSENSITIVE);
        boolean andFlag = splitRule1.matcher(filter).find();
        boolean orFlag = splitRule2.matcher(filter).find();
        if(andFlag && orFlag){//有%or和%and
            return convertCompoundCondition(filter, timeZone, splitStr[0], splitRule1, splitStr[1], splitRule2);
        }
        else if(!orFlag){//没有%or
            //优化首尾，首尾括号一定是一对,括号暂时没有和输入内容中的括号区分，只管单一条件即可
            if(filter.matches("^( {0,}\\(.*)") && filter.matches("(.*\\) {0,})$")){
                filter = filter.replaceAll("^ {0,}\\(|\\) {0,}$", "");
            }
            return convertCompoundCondition(filter, timeZone, splitStr[0], splitRule1, null, null);
        }else if(!andFlag){//没有%and
             //优化首尾，首尾括号一定是一对,括号暂时没有和输入内容中的括号区分，只管单一条件即可
            if(filter.matches("^( {0,}\\(.*)") && filter.matches("(.*\\) {0,})$")){
                filter = filter.replaceAll("^ {0,}\\(|\\) {0,}$", "");
            }
            return convertCompoundCondition(filter, timeZone, null, null, splitStr[1], splitRule2);
        }
        return null;        
    }
    
    /**
     * 情况1转换or和and复合条件,同时存在两种条件，or条件必须用"()"包起来，
     * 情况2转换and
     * 情况3转换or
     */
    public static String convertCompoundCondition(String filter,String timeZone,String conditionStr1
            ,Pattern splitRule1,String conditionStr2,Pattern splitRule2) {
        StringBuilder buf = new StringBuilder();
        String conditionStr = "";
        Pattern splitRule = null;
        if(StrUtil.isNotBlank(conditionStr1)){
            conditionStr = conditionStr1;
            splitRule = splitRule1;
        }else{
            conditionStr = conditionStr2;
            splitRule = splitRule2;
        }
        String[] arr = splitRule.split(filter);
        for (int i = 0; i < arr.length; i++) {
            String ar = arr[i].trim();
            //System.out.println("条件拆分:: " + ar);
            //如果首尾为括号则表示需要再次拆分,现在只管嵌套or拆分,and没必要括号
            if(ar.startsWith("(") && ar.endsWith(")")){
                String tempFilter = ar.substring(1, ar.length()-1);
                String tempCondition = convertCompoundCondition(tempFilter, timeZone, null, null, conditionStr2, splitRule2);
                if (StrUtil.isNotBlank(tempCondition) && buf.length() > 0) {
                    buf.append(" "+conditionStr1+" (" + tempCondition+")");
                } else if (StrUtil.isNotBlank(tempCondition)){
                    buf.append("(" +tempCondition+")");
                }        
            }
            
            Matcher m = FILTER_RULE.matcher(ar);
            if (m.find()) {// 一个条件只有一个运算符
                String findKey = m.group();

                String[] query = ar.split(findKey);
                if (query.length != 2) {// 只有 KEY 和 VALUE
                    continue;
                }
                query[0] = query[0].trim();// 去除首尾空格
                query[1] = query[1].trim();// 去除内容中的单引号和首尾空格
                
                // 判断是否有非法语句
                Matcher nameMH = FIELD_FILTER_RULE.matcher(query[0]);
                if (nameMH.find()) {// 字段名校验不通过
                    continue;
                }
                // 获取内部运算符号
                String inSymbol = FILTER_MAP.get(findKey.trim());
                if (inSymbol == null) {// 运算符不符合
                    continue;
                }
                
                String newQuery;
                // 判断是否按时间处理
                Matcher timeMH = TIME_CHECK_RULE.matcher(query[1]);
                if (timeMH.find()) {
                    query[1] = timeMH.group().substring(5, timeMH.group().length() - 1);
                    query[0] = String.format("date_format(`%s`,'%s')", query[0].trim(),TIME_FORMAT_MAP.get(String.valueOf(query[1].length())));
                    
                    if(query[1].length() == 14 && StrUtil.isNotBlank(timeZone)){//页面查询带时分秒会有时间差异,时区转到系统时区
                        //TODO
                        query[1] = DateUtil.toLocalDateTime(DateUtil.parse(query[1])).toString();
                    }
                    newQuery = String.format("%s %s \"%s\"", query[0].trim(), inSymbol, query[1].trim());
                } else {
                    Matcher specialMather = SPECIAL_FILTER_RULE.matcher(query[1]);
                    if(specialMather.find()){
//                        continue;
                        query[1] = "";//对于条件有特殊字符的直接置为空
                    }
                    query[1] = escapeSQL(query[1]);
                    
                    if(StrUtil.isNotBlank(query[1])){
                        if (findKey.trim().equals("lk") || findKey.trim().equals("nk")) {
                            newQuery = String.format("`%s` %s \"%s\"", query[0].trim(), inSymbol, "%" + query[1].trim() + "%");
                        } else if(findKey.trim().equals("cn")) {
                            String querys = "";
                            for(String temp:query[1].trim().split(",")) {
                                querys = querys + "\""+temp+"\",";
                            }
                            querys = querys.substring(0, querys.length()-1);
                            newQuery = String.format("`%s` %s ( %s )", query[0].trim(), inSymbol, querys);
                        } else {
                            newQuery = String.format("`%s` %s \"%s\"", query[0].trim(), inSymbol, query[1].trim());
                        }
                    }else{
                        newQuery = String.format("%s = \"   \"", query[0].trim()); //对于条件有特殊字符的强制等于空,提高效率
                    }
                    
                }

                if (buf.length() > 0) {
                    buf.append(" "+conditionStr+" " + newQuery);
                } else {
                    buf.append(newQuery);
                }
            }
        }
        return buf.toString();
    }
    
    /**还是转义下比较好吧*/
    public static String escapeSQL(String s){
        int length = s.length();
        int newLength = length;
        // first check for characters that might
        // be dangerous and calculate a length
        // of the string that has escapes.
        for (int i=0; i<length; i++){
          char c = s.charAt(i);
          switch(c){
            case '\\':
            case '\"':
            case '\'':
            case '\0':{
              newLength += 1;
            } break;
          }
        }
        if (length == newLength){
          // nothing to escape in the string
          return s;
        }
        StringBuffer sb = new StringBuffer(newLength);
        for (int i=0; i<length; i++){
          char c = s.charAt(i);
          switch(c){
            case '\\':{
              sb.append("\\\\");
            } break;
            case '\"':{
              sb.append("\\\"");
            } break;
            case '\'':{
              sb.append("\\\'");
            } break;
            case '\0':{
              sb.append("\\0");
            } break;
            default: {
              sb.append(c);
            }
          }
        }
        return sb.toString();
      }
    
}
